package com.scau.RBAC.dao.impl;

import com.scau.RBAC.dao.UserDao;
import com.scau.RBAC.model.entity.User;
import com.scau.RBAC.util.DBConnectionPool;
import com.scau.RBAC.util.DatabaseException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * PostgreSQL数据库的UserDao实现
 */
public class PostgreSqlUserDao implements UserDao {
    private final QueryRunner queryRunner;

    public PostgreSqlUserDao() {
        DataSource dataSource = DBConnectionPool.getDataSource();
        this.queryRunner = new QueryRunner(dataSource);
    }

    @Override
    public int insert(User entity) {
        try {
            return queryRunner.update(
                    "INSERT INTO users(username, password, full_name, email, phone, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    entity.getUsername(), entity.getPassword(), entity.getFullName(), entity.getEmail(), entity.getPhone(),
                    entity.getStatus(), entity.getCreatedTime(), entity.getUpdatedTime(), entity.getCreatedBy(), entity.getUpdatedBy()
            );
        } catch (SQLException e) {
            throw new DatabaseException("插入用户失败", e);
        }
    }

    @Override
    public int batchInsert(List<User> entities) {
        try {
            Object[][] params = new Object[entities.size()][10];
            for (int i = 0; i < entities.size(); i++) {
                User user = entities.get(i);
                params[i][0] = user.getUsername();
                params[i][1] = user.getPassword();
                params[i][2] = user.getFullName();
                params[i][3] = user.getEmail();
                params[i][4] = user.getPhone();
                params[i][5] = user.getStatus();
                params[i][6] = user.getCreatedTime();
                params[i][7] = user.getUpdatedTime();
                params[i][8] = user.getCreatedBy();
                params[i][9] = user.getUpdatedBy();
            }

            int[] results = queryRunner.batch(
                    "INSERT INTO users(username, password, full_name, email, phone, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    params
            );

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            throw new DatabaseException("批量插入用户失败", e);
        }
    }

    @Override
    public int deleteById(Long id) {
        try {
            return queryRunner.update("DELETE FROM users WHERE id = ?", id);
        } catch (SQLException e) {
            throw new DatabaseException("删除用户失败", e);
        }
    }

    @Override
    public int batchDelete(List<Long> ids) {
        try {
            Object[][] params = new Object[ids.size()][1];
            for (int i = 0; i < ids.size(); i++) {
                params[i][0] = ids.get(i);
            }

            int[] results = queryRunner.batch("DELETE FROM users WHERE id = ?", params);

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            throw new DatabaseException("批量删除用户失败", e);
        }
    }

    @Override
    public int update(User entity) {
        try {
            return queryRunner.update(
                    "UPDATE users SET username = ?, password = ?, full_name = ?, email = ?, phone = ?, status = ?, last_login_time = ?, updated_time = ?, updated_by = ? WHERE id = ?",
                    entity.getUsername(), entity.getPassword(), entity.getFullName(), entity.getEmail(), entity.getPhone(),
                    entity.getStatus(), entity.getLastLoginTime(), entity.getUpdatedTime(), entity.getUpdatedBy(), entity.getId()
            );
        } catch (SQLException e) {
            throw new DatabaseException("更新用户失败", e);
        }
    }

    @Override
    public User findById(Long id) {
        try {
            return queryRunner.query("SELECT * FROM users WHERE id = ?", new BeanHandler<>(User.class), id);
        } catch (SQLException e) {
            throw new DatabaseException("根据ID查询用户失败", e);
        }
    }

    @Override
    public List<User> findAll() {
        try {
            return queryRunner.query("SELECT * FROM users", new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            throw new DatabaseException("查询所有用户失败", e);
        }
    }

    @Override
    public List<User> findByPage(Map<String, Object> params, int pageNum, int pageSize) {
        try {
            StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");
            Object[] queryParams = buildQueryParams(sql, params);

            // PostgreSQL的分页语法
            sql.append(" LIMIT ? OFFSET ?");
            Object[] finalParams = new Object[queryParams.length + 2];
            System.arraycopy(queryParams, 0, finalParams, 0, queryParams.length);
            finalParams[queryParams.length] = pageSize;
            finalParams[queryParams.length + 1] = (pageNum - 1) * pageSize;

            return queryRunner.query(sql.toString(), new BeanListHandler<>(User.class), finalParams);
        } catch (SQLException e) {
            throw new DatabaseException("分页查询用户失败", e);
        }
    }

    @Override
    public long count(Map<String, Object> params) {
        try {
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM users WHERE 1=1");
            Object[] queryParams = buildQueryParams(sql, params);

            return queryRunner.query(sql.toString(), rs -> {
                if (rs.next()) {
                    return rs.getLong(1);
                }
                return 0L;
            }, queryParams);
        } catch (SQLException e) {
            throw new DatabaseException("统计用户数量失败", e);
        }
    }

    @Override
    public User findByUsername(String username) {
        try {
            return queryRunner.query("SELECT * FROM users WHERE username = ?", new BeanHandler<>(User.class), username);
        } catch (SQLException e) {
            throw new DatabaseException("根据用户名查询用户失败", e);
        }
    }

    @Override
    public List<User> findByRoleId(Long roleId) {
        try {
            return queryRunner.query(
                    "SELECT u.* FROM users u INNER JOIN user_roles ur ON u.id = ur.user_id WHERE ur.role_id = ?",
                    new BeanListHandler<>(User.class), roleId
            );
        } catch (SQLException e) {
            throw new DatabaseException("根据角色ID查询用户失败", e);
        }
    }

    // 构建查询参数的辅助方法
    private Object[] buildQueryParams(StringBuilder sql, Map<String, Object> params) {
        // 简化实现，实际应用中需要根据具体需求进行扩展
        return new Object[0];
    }
}